# -*- coding:utf-8 -*-
import os

import tushare as ts
import talib as ta
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
#import tensorflow as tf
import numpy


import MySQLdb
import random
import requests
import time
import json
import sys
import gevent
from gevent import monkey
gevent.monkey.patch_all()


conn=None

def get():
	conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='MjU5YjE3ZGUw',port=3306)
	cur=conn.cursor()
	sql="select * from mae.dockerfile_template_info"
	'''
	value=[1,'hi rollen']
    cur.execute('insert into test values(%s,%s)',value)
	'''
	while True:
		cur.execute(sql)
		result=cur.fetchone()
		print result


def insert():
	conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='YTk3NWNjMWMy',port=3306)
	cur=conn.cursor()
	count=0
	sql='insert into mytest.image_info(image_id,project_id,git_tag,image_md5)values(%s,%s,%s,%s)'
	for item in range(0,1000000,1):
		value=(str(random.random()),"project_id","git_tag","image_md5")
		cur.execute(sql,value)
		conn.commit()
		count=count+1
		if count%1000==0:
			print count
	cur.close()
	conn.close()
	#

def get_stock_values(stock_code, date):
	if stock_code[0]=='6':
		stock_code='sh'+stock_code
	else:
		stock_code='sz'+stock_code
	paramdict=[]
	cookies={}
	headers={}

	#r = requests.get("http://hq.sinajs.cn/list="+stock_code,cookies=cookies,headers=headers)
	r = requests.get("http://market.finance.sina.com.cn/downxls.php?date=2016-09-14&symbol=sz000001",cookies=cookies,headers=headers)
	#r = requests.get("hq.sinajs.cn/list="+stock_code, paramdict)
	#http://hq.sinajs.cn/list=sh601006
	print 'text:',r.text
	poshead=r.text.find('"')
	postail=r.text.find('"',poshead+1)
	print poshead,postail
	strjson=r.text[int(poshead):int(postail+1)]
	print strjson
	pass


#获取股票历史数据
def get_stock_hs_data(stock,startdate):
	#print 'get_stock_hs_data  ',stock, startdate
	#return []
	#新的数据在前面，老的在后面，每列分别为 open   high  close    low      volume       amount
	df = ts.get_h_data(stock,start=startdate)
	#print df.values
	#return []
	#if df.values==None:
	#	return []
	stocklist=[]
	for index in range(0,len(df.values),1):
		v=(str(df.axes[0][index]).replace('-','')[:8],list(df.values[index]))
		#print df.values[index]
		#开盘价 最高价  当前价(收盘价)  最低价  成交量 涨跌绝对值  涨跌百分比
		stocklist.append(v)
		#break
	#print df
	#print df.values[:df.shape[0]]
	#print stocklist
	#print len(stocklist)
	#print stocklist
	#return []
	return stocklist
	#df = ts.get_h_data(stock,start='2017-01-01')
	#np.savetxt("data_0407/"+stock+".txt", df.values[:df.shape[0]])
	#print df
	#print df.shape
	#print '------'
	#print df.values[0]
	#print df.values[0][0]
	#print df.values[0][1]
	#print '------'
	#print df.values[:df.shape[0]]
	#print '------'
	#print df.values[:df.shape[1]]

def save_stock_info(stock_code,stockinfo):
	global conn
	date=stockinfo[0]
	open  =stockinfo[1][0]
	high  =stockinfo[1][1]
	close =stockinfo[1][2]
	low   =stockinfo[1][3]
	volume=stockinfo[1][4]
	amount=stockinfo[1][5]

	#price_change=stockinfo[1][5]
	#p_change=stockinfo[1][6]
	cur=conn.cursor()
	count=0
	sql='insert into stock.stock_info(stock_code,stock_date,open,close,high,low,volume,amount)values(%s,%s,%s,%s,%s,%s,%s,%s)'
	value=(stock_code,date,open,close,high,low,volume,amount)
	cur.execute(sql,value)
	conn.commit()
	return

def init_conn():
	global conn
	conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='Midea@123',port=3306)
	print 'mysql conn finish'

def get_stock_base():
	df = ts.get_stock_basics()
	stockbase_list=[]
	for index in range(0,len(df.values),1):
		v=(str(df.axes[0][index]),df.values[index][0])
		stockbase_list.append(v)
	return stockbase_list

def save_stock_base(stockbase_list):
	global conn
	for item in stockbase_list:
		stock_code=item[0]
		stock_name=str(item[1])
		cur=conn.cursor()
		sql='insert into stock.stock_base(stock_code,stock_name)values(%s,%s)'
		value=(stock_code,stock_name)
		cur.execute(sql,value)
		conn.commit()
	return

def load_all_stock_base():
	global conn
	stocklist=[]
	cur=conn.cursor()
	sql="select stock_code from stock.stock_base"
	cur.execute(sql)
	while True:
		result=cur.fetchone()
		if result==None:
			break
		code=result[0]
		stocklist.append(code)
	return stocklist

if __name__=='__main__':
	#get()
	#insert()
	print 'hello'
	init_conn()
	#mylist =load_all_stock_base()
	#print mylist
	stockbase_list = get_stock_base()
	#save_stock_base(stockbase_list)
	#exit(0)
	#get_stock_values('601006','20170404')
	needsync=0
	for stock_code in stockbase_list:
		print 'ready to sync stock '+stock_code[0]
		stocklist=get_stock_hs_data(stock_code[0],'2016-01-01')
		if stock_code[0]=='002841':
			needsync=1
			continue
		if needsync==0:
			continue

	#for item in stocklist:
	#	print item
	#	break
	#exit(0)
	#print stocklist[0]
		for item in stocklist:
			#stock_code='000011'
			#stockinfo=item[1]
			save_stock_info(stock_code[0],item)
			#print item
			#print '----'
			#break
		#print 'save finish'
































